package com.wandian.texas.demo;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.concurrent.TimeUnit;

import com.mysql.jdbc.ReplicationDriver;
import com.wandian.texas.db.DbWatch;
import com.wandian.texas.entity.UserInfoEntity;
import com.wandian.util.Config;
import com.wandian.util.DESUtil;
import com.wandian.util.Log;

/**
 * @author long
 * @Create 2012-10-10 下午5:06:33
 * @QQ 108232706
 * @version 1.0
 * @description DbConnectionTest
 */
public class DbConnectionTest {
	private static String configPath = "E:/work/gameserver/src/main/resources/config/config.properties";
	public static String WANDIAN_POOL_NAME = "mysql";
	private static final String DEFAULT_DB_NAME = "mysql";
	private static Connection[] connections;
	private static CmdThread[] threads;
	private static int threadNum = 5;
	public static int addCount = 10000;
	public static boolean isConnected = false;
	private static final DbWatch watch = new DbWatch();
	private static short idle_status = 0;
	// 负责执行命令的线程正在执行命令，表明忙
	private static short ready_exec_status = 1;
	private static short ready_pause_status = 2;

	static class CmdThread extends Thread {
		public int index;
		public boolean isRun;
		public short _status;
		public int curCmd;

		public CmdThread(int _index) {
			super("CmdThread" + _index);
			index = _index;
			_status = idle_status;
		}

		@Override
		public void run() {
			while (isRun) {
				if (_status == ready_exec_status) {
					switch (curCmd) {
					case 1:
						DbConnectionTest.testInsertTb1(this.index);
						break;
					case 2:
						DbConnectionTest.testInsertTb2(this.index);
						break;
					case 3:
						DbConnectionTest.testBatchInsertTb1(this.index);
						break;
					case 4:
						DbConnectionTest.testBatchInsertTb2(this.index);
						break;
					default:
						System.out.println("命令无效!");
						break;
					}
					_status = idle_status;
					System.out.println("线程执行命令完成,id:" + this.index + " cmdid:" + this.curCmd);
				}

				try {
					TimeUnit.MILLISECONDS.sleep(1);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public static boolean initConnection() {
		// 获取数据库驱动Driver以及连接配置
		connections = new Connection[threadNum];
		threads = new CmdThread[threadNum];
		Properties props = initDb(Config.getValue("db.path"));
		String url = props.getProperty(WANDIAN_POOL_NAME + ".url");
		String user = DESUtil.decrypt(props.getProperty(WANDIAN_POOL_NAME + ".user"));
		String password = DESUtil.decrypt(props.getProperty(WANDIAN_POOL_NAME + ".password"));
//		String maxconn = props.getProperty(WANDIAN_POOL_NAME + ".maxconn", "5");
		String driver1 = props.getProperty(DEFAULT_DB_NAME + ".driver");
		isConnected = true;
		try {
			Class.forName(driver1);
		} catch (ClassNotFoundException e) {
			Log.error("初始化数据库连接池错误", e);
			isConnected = false;
		}
		// 创建Connection
		try {
//			DriverManager.setLoginTimeout(5);
			watch.start();
			for (int i = 0; i < threadNum; i++) {
				connections[i] = DriverManager.getConnection(url, user, password);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			isConnected = false;
		} finally {
			watch.commit("[DriverManager.getConnection]");
		}
//		connection.setAutoCommit(false);
		for (int i = 0; i < threadNum; i++) {
			threads[i] = new CmdThread(i);
			threads[i]._status = idle_status;
			threads[i].isRun=true;
			threads[i].start();
		}
		return isConnected;
	}

	private static ReplicationDriver driver;

	public static void initRepConnection() throws SQLException {
		Properties props = new Properties();
		// We want this for failover on the slaves	
		props.put("autoReconnect", "true");
		// We want to load balance between the slaves

		props.put("roundRobinLoadBalance", "true");
		props.put("user", "foo");
		props.put("password", "bar");

		Connection conn = driver.connect("", props);
		// Perform read/write work on the master
		// by setting the read-only flag to "false"
		conn.setReadOnly(false);
		conn.setAutoCommit(false);
		conn.createStatement().executeUpdate("UPDATE some_table ....");
		conn.commit();
		//
		// Now, do a query from a slave, the driver automatically picks one
		// from the list
		//
		conn.setReadOnly(true);
		/*ResultSet rs =*/conn.createStatement().executeQuery("SELECT a,b FROM alt_table");
	}

	public static int executeNoQuery(String produceName, int dbType, Object... params) {
		int result = 0;
		CallableStatement stmt = null;
		try {
			stmt = execProduce2(produceName, params);
			if (stmt != null) {
				result = stmt.executeUpdate();
				// System.out.println("id " + stmt.getGeneratedKeys());
			}
		} catch (Exception e) {
			e.printStackTrace();
			Log.error("[DataBase]执行出错" + produceName, e);
			result = -11;
		} finally {
			closeConn(stmt);
			params = null;
		}

		return result;
	}

	public static void testMySql() {
		//测试user表插入1w数据的时间
		System.out.println("==========测试mysql 插入开始=========");
		int[] result = sqlBatch("insert into user(name) values (?);", 10000, "test");
		if (result == null)
			return;
		int error = 0;
		for (int i = 0; i < result.length; i++) {
			if (result[i] < 1)
				error++;
		}
		System.out.println("错误总数:" + error);
	}

	public static void testInsert() {
		Connection conn = DbConnectionTest.connections[0];
		watch.start();
		int result = 0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement("insert into user(name) values (?)", 1);
			prepareCommand2(pstmt, "testInsert");
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			if (rs.next())
				result = rs.getInt(1);
		} catch (Exception ex) {
			String msg = "获取自增id出错";
			Log.error(msg, ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			Log.info("执行耗时ms:" + watch.commit() + ",id:" + result);
		}
	}

	public static void testInsertTb1() {

	}

	public static void testInsertTb1(int index) {
		Connection conn = DbConnectionTest.connections[index];
		watch.start();
		int result = 0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			conn.setAutoCommit(false);
			for (int i = 0; i < addCount; i++) {
				pstmt = conn.prepareStatement(" insert into tb1(num,account)values(?,'tb1')", 1);
				prepareCommand2(pstmt, i);
				pstmt.executeUpdate();
//				pstmt.executeUpdate();
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			Log.error(ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			Log.info("线程："+index+"testInsertTb1 " + addCount + "条执行耗时ms:" + watch.commit() + ",id:" + result);
		}
	}

	public static void testInsertTb2(int index) {
		Connection conn = DbConnectionTest.connections[index];
		watch.start();
		int result = 0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			conn.setAutoCommit(false);
			for (int i = 0; i < addCount; i++) {
				pstmt = conn.prepareStatement(" insert into tb1(num,account)values(?,'tb1')", 1);
				prepareCommand2(pstmt, i);
				pstmt.executeUpdate();
//				pstmt.executeUpdate();
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			Log.error(ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			Log.info("线程："+index+"testInsertTb2 " + addCount + "条执行耗时ms:" + watch.commit() + ",id:" + result);
		}
	}

	public static void testBatchInsertTb1() {
		for (int i = 0; i < threadNum; i++) {
			if (threads[i]._status == idle_status) {
				threads[i].curCmd = 3;
				threads[i]._status=ready_exec_status;
			}
		}
	}
	public static void testThreads(int cmdId) {
		for (int i = 0; i < threadNum; i++) {
			if (threads[i]._status == idle_status) {
				threads[i].curCmd = cmdId;
				threads[i]._status=ready_exec_status;
			}
		}
	}
	public static void testBatchInsertTb1(int index) {
		Connection conn = DbConnectionTest.connections[index];
		watch.start();
		int result = 0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(" insert into tb1(num,account)values(?,'tb1')", 1);
			for (int i = 0; i < addCount; i++) {
				pstmt.addBatch("insert into tb1(num,account)values(" + i + ",'tb1');");
			}
			pstmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			Log.error(ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			Log.info("线程："+index+"testBatchInsertTb1 " + addCount + "条执行耗时ms:" + watch.commit() + ",id:" + result);
		}
	}

	public static void testBatchInsertTb2(int index) {
		Connection conn = DbConnectionTest.connections[index];
		watch.start();
		int result = 0;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		try {
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(" insert into tb1(num,account)values(?,'tb1')", 1);
			for (int i = 0; i < addCount; i++) {
				pstmt.addBatch("insert into tb2(num,account)values(" + i + ",'tb1');");
			}
			pstmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			Log.error(ex);
			result = -1;
		} finally {
			closeConn(conn, pstmt, rs);
			Log.info("线程："+index+"testBatchInsertTb2 " + addCount + "条执行耗时ms:" + watch.commit() + ",id:" + result);
		}
	}

	private static void closeConn(Connection conn, Statement pstmt, ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
			Log.error("关闭数据库出错", e);
		}
		closeConn(conn, pstmt);
	}

	public static int[] sqlBatch(String sql, int batchCount, Object... params) {
		watch.start();
		Connection conn = DbConnectionTest.connections[0];
		//重连
//		if(!isConnected){
//			
//		}
		watch.getPool();
		if (conn == null) {
			watch.commit(sql);
			return null;
		}
		PreparedStatement pstmt = null;
		int[] result = null;
		try {
			conn.setAutoCommit(false);
			// 预编译SQL语句,只编译一回,效率高啊
			pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < batchCount; i++) {
				prepareAddBatch(pstmt, params);
			}
			result = pstmt.executeBatch();
			conn.setAutoCommit(true);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + sql + "出错" });
			Log.error(msg, ex);
			closeConn(conn, pstmt);
		} finally {
			Log.info("sqlBatch,执行耗时ms:" + watch.commit());

		}
		return result;
	}

	protected static void prepareAddBatch(PreparedStatement pstmt, Object... parms) throws SQLException {
		if ((pstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			pstmt.setObject(i, parms[i - 1]);
		}
		pstmt.addBatch();
	}

	protected static CallableStatement execProduce2(String produceName, Object... params) {
		watch.start();
		CallableStatement stmt = null;
		try {
			stmt = connections[0].prepareCall(ProcedureName(produceName, params.length), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareCallable2(stmt, params);
		} catch (Exception ex) {
			String msg = String.format("[DataBase] 提示消息:%s; 错误描述:%s; 当前状态:%s", new Object[] { "当前Sql语句出错", ex.getMessage(), "调用Sql语句" + produceName + "出错" });
			Log.error(msg + connections[0], ex);
			closeConn(connections[0], stmt);

		} finally {
			watch.commit(produceName);
		}
		return stmt;
	}

	protected static void prepareCallable2(CallableStatement callstmt, Object... parms) throws SQLException {
		if ((callstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			callstmt.setObject(i, parms[i - 1]);
		}
	}

	public static int executeProcedureReturn(String produceName, int dbType, Object... params) {
		int result = -100;
		Connection conn = null;
		CallableStatement stmt = null;
		try {
			if (conn == null) {
				return result;
			}
			stmt = conn.prepareCall(ProcedureName(produceName, 1, params.length), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			prepareFreeCallable(stmt, 1, params);
			stmt.execute();
			result = stmt.getInt(1);
			// result = stmt.getInt(1);
		} catch (Exception e) {
			Log.error("[DataBase]执行出错" + produceName, e);
			result = -100;
		} finally {
			closeConn(stmt);
		}

		return result;
	}

	protected static void closeConn(Statement pstmt) {
		try {
			if (pstmt != null) {
				Connection conn = pstmt.getConnection();
				closeConn(conn, pstmt);
			}
		} catch (SQLException e) {
			Log.error("关闭db连接时异常", e);
		}
	}

	private static void closeConn(Connection conn, Statement statement) {
		if (statement != null) {
			try {
				statement.clearBatch();
				statement.close();
			} catch (SQLException e) {
				Log.error("关闭数据库出错", e);
			}
		}
		closeConn(conn);
	}

	private static void closeConn(Connection conn) {

	}

	protected static String ProcedureName(String procName, int isOutPut, int paraCount) {
		String procedureName = "";
		if (isOutPut > 0) {
			procedureName = ProcedureNameByReturn(procName, paraCount);
		} else {
			procedureName = ProcedureName(procName, paraCount);
		}
		return procedureName;
	}

	protected static String ProcedureName(String procName, int paraCount) {
		int paramCount = paraCount;
		String questionMark = "";

		for (int i = 0; i < paramCount; i++) {
			questionMark = questionMark + "?,";
		}
		if (questionMark != "") {
			questionMark = questionMark.substring(0, questionMark.length() - 1);
		}
		return "{call  " + procName + "(" + questionMark + ")}";
	}

	protected static String ProcedureNameByReturn(String procName, int paraCount) {
		int paramCount = paraCount;
		String questionMark = "";

		for (int i = 0; i < paramCount; i++) {
			questionMark = questionMark + "?,";
		}
		if (questionMark != "") {
			questionMark = questionMark.substring(0, questionMark.length() - 1);
		}
		return "{?=call  " + procName + "(" + questionMark + ")}";
	}

	private static void prepareCommand2(PreparedStatement pstmt, Object... parms) throws SQLException {
		if ((pstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= parms.length; i++) {
			pstmt.setObject(i, parms[i - 1]);
		}
	}

	protected static void prepareFreeCallable(CallableStatement callstmt, int isOutPut, Object... parms) throws SQLException {
		if ((callstmt == null) || (parms == null))
			return;
		for (int i = 1; i <= isOutPut; i++) {
			callstmt.registerOutParameter(i, java.sql.Types.INTEGER);
		}
		int totalParams = parms.length;
		if (isOutPut > 0)
			totalParams++;
		for (int i = 1 + isOutPut; i <= totalParams; i++) {
			callstmt.setObject(i, parms[i - 1 - isOutPut]);
		}
	}

	public UserInfoEntity getUserInfo(int userId) throws SQLException {
		UserInfoEntity info = null;
		if (!initComponent(Config.initConfig(configPath), "加载配置文件")) {
			return null;
		}
		// 获取数据库驱动Driver以及连接配置
		Properties props = initDb(Config.getValue("db.path"));
		String url = props.getProperty(WANDIAN_POOL_NAME + ".url");
		String user = DESUtil.decrypt(props.getProperty(WANDIAN_POOL_NAME + ".user"));
		String password = DESUtil.decrypt(props.getProperty(WANDIAN_POOL_NAME + ".password"));
//		String maxconn = props.getProperty(WANDIAN_POOL_NAME + ".maxconn", "5");
		String driver = props.getProperty(DEFAULT_DB_NAME + ".driver");
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			Log.error("初始化数据库连接池错误", e);
			return null;
		}
		// 创建Connection
		Connection connection = DriverManager.getConnection(url, user, password);
		connection.setAutoCommit(false);
		String sqlText = "select * from t_userinfo WHERE  userId=? ;";
		// 通过connection对象获取一个PreparedStatement
		PreparedStatement statement = connection.prepareStatement(sqlText);
		// 给语句赋参数
		statement.setObject(1, userId);
		// 执行查询
		ResultSet resultSet = statement.executeQuery();
		if (resultSet.last()) {
			info = new UserInfoEntity();
			info.setUserId(resultSet.getInt("UserId"));
			info.setNickName(resultSet.getString("NickName"));
		}
		// 关闭连接
		// 清除数据集
		if (resultSet != null) {
			resultSet.close();
			resultSet = null;
		}
		// 关闭statement
		if (statement != null) {
			statement.clearParameters();
			statement.close();
			statement = null;
		}
		// 关闭connection
		if (connection != null) {
			connection.close();
			connection = null;
		}
		return info;
	}

	public boolean delChatrecord(int Id) throws SQLException {
		boolean succ = false;
		if (!initComponent(Config.initConfig(configPath), "加载配置文件")) {
			return false;
		}
		Properties props = initDb(Config.getValue("db.path"));
		String url = props.getProperty(WANDIAN_POOL_NAME + ".url");
		String user = DESUtil.decrypt(props.getProperty(WANDIAN_POOL_NAME + ".user"));
		String password = DESUtil.decrypt(props.getProperty(WANDIAN_POOL_NAME + ".password"));
		String maxconn = props.getProperty(WANDIAN_POOL_NAME + ".maxconn", "5");
		String driver = props.getProperty(DEFAULT_DB_NAME + ".driver");
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			Log.error("初始化数据库连接池错误", e);
			return false;
		}
		// 创建Connection
		Connection connection = DriverManager.getConnection(url, user, password);
		connection.setAutoCommit(false);
		String sql = "delete from t_Chatrecord where `Id`=?; ";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setObject(1, Id);
		// 执行查询
		int result = statement.executeUpdate();
		if (result > 0) {
			succ = true;
		}
		// 关闭连接
		if (statement != null) {
			statement.clearParameters();
			statement.close();
			statement = null;
		}
		if (connection != null) {
			connection.close();
			connection = null;
		}
		return succ;
	}

	public static Properties initDb(String dbPath) {
		InputStream fileinputstream = null;
		try {
			fileinputstream = new FileInputStream(dbPath);
		} catch (Exception e) {
			String msg = String.format("未找到DB配置文件%s", new Object[] { dbPath });
			Log.error(msg, e);
		}
		Properties dbProps = new Properties();
		try {
			dbProps.load(fileinputstream);
		} catch (Exception e) {
			String msg = String.format("打开文件%s出错", new Object[] { dbPath });
			Log.error(msg, e);
			return null;
		}

		return dbProps;
	}

	public boolean initComponent(boolean initResult, String componentName) {
		if (!initResult)
			Log.error(componentName + "错误");
		else {
			Log.info(componentName + "加载完成");
		}
		return initResult;
	}
}
